CREATE PROCEDURE [dbo].[asi_GetRecentCompletePublishRequests]
    @PublishServerCode nchar(1),
    @NumberOfRequests int = 20
AS
BEGIN
    DECLARE @recentRequests TABLE (
        ID int identity (1,1),
        PublishRequestKey uniqueidentifier
    )

    INSERT INTO @recentRequests (PublishRequestKey)
    SELECT PublishRequestKey FROM PublishRequest
    WHERE Exists (SELECT 1 FROM PublishRequestDetail prd, DocumentMain dm
                          WHERE prd.PublishRequestKey = PublishRequest.PublishRequestKey
                            AND prd.PublishServerCode = @PublishServerCode
                            AND prd.ContentDocumentKey = dm.DocumentKey
                            AND ((dm.DocumentStatusCode IN (30,40) AND prd.IsDelete = 0)
                                    OR (dm.DocumentStatusCode IN (70,80) AND prd.IsDelete = 1))
                           AND prd.PublishRequestStatusCode <> 0
                   )
    ORDER BY RequestDateTime DESC

    SELECT PublishRequestKey FROM @recentRequests WHERE ID <= @NumberOfRequests
END


SET ANSI_NULLS ON

GO